package com.mfp.java.testYou.db.mySql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mfp.java.testYou.db.DataAccessObject;
import com.mfp.java.testYou.db.UnauthorizedException;
import com.mfp.java.testYou.db.model.Answer;
import com.mfp.java.testYou.db.model.CompletedTest;
import com.mfp.java.testYou.db.model.Question;
import com.mfp.java.testYou.db.model.Test;
import com.mfp.java.testYou.db.model.User;

public class DBConnectionDAO implements DataAccessObject {

	private Statement statement;
	private Connection con;

	public DBConnectionDAO() throws IllegalAccessException, InstantiationException, ClassNotFoundException, SQLException {
		con = MySQLConnect.startDBConnection();
	}

	@Override
	public List<User> getAllUsers() {
		List<User> listUsers = new ArrayList();
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM user");
			User user;
			while (rs.next()) {
				user = ObjectsUtil.createUser(rs);
				listUsers.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listUsers;
	}

	@Override
	public User getUser(String username, CharSequence password) throws UnauthorizedException {
		User u = null;
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM user WHERE username='" + username + "'");
			System.out.println(rs.toString());
			if (!rs.next()) {
				throw new UnauthorizedException();
			}
			if (!ObjectsUtil.passwordIsCorrect(password.toString(), rs.getString(3))) {
				throw new UnauthorizedException();
			}
			u = ObjectsUtil.createUser(rs);
			System.out.println("Pass:" + ObjectsUtil.passwordIsCorrect(password.toString(), rs.getString(3)) + " " + rs.getString(3) + " " + password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("PASSWORD ENCRYPTION NOT RECOGNIZED!!!");
			;
		}
		return u;
	}

	@Override
	public User getUser(Integer userId) throws IllegalArgumentException {
		User user = null;
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM user WHERE user_id=" + userId);
			rs.next();
			user = ObjectsUtil.createUser(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return user;
	}

	@Override
	public User addUser(User user, CharSequence pass) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		String encryptedPassword = ObjectsUtil.encryptPass(pass);
		System.out.println(encryptedPassword);
		String sql = "INSERT INTO user(username,password,user_lang,user_role) VALUES ('" + user.getUserName() + "','" + encryptedPassword + "','" + user.getLang() + "','" + user.getRole() + "')";
		statement.executeUpdate(sql);
		ResultSet rs = statement.executeQuery("SELECT * FROM user WHERE username='" + user.getUserName() + "'");
		rs.next();
		user.setId(rs.getInt(1));
		return user;
	}

	@Override
	public void deleteUser(Integer userId) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		int delete = statement.executeUpdate("DELETE FROM user where user_id=" + userId);
		if (delete == 1)
			System.out.println("User was deleted");
		else
			System.out.println("User was not deleted");
	}

	@Override
	public User editUser(User newUserData, CharSequence pass, int userId) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		String encryptedPassword = ObjectsUtil.encryptPass(pass);
		statement.executeUpdate("UPDATE user SET " + "username='" + newUserData.getUserName() + "',password='" + encryptedPassword + "',user_lang='" + newUserData.getLang() + "',user_role='" + newUserData.getRole() + "' WHERE user_id=" + userId);
		return newUserData;
	}

	@Override
	public List<Question> getAllQuestions() {
		List<Question> listQuestions = new ArrayList();
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM question");
			Question question;
			while (rs.next()) {
				question = ObjectsUtil.createQuestion(rs);
				question.setAnswers(getAnswers(question.getId()));
				listQuestions.add(question);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listQuestions;
	}

	@Override
	public Question getQuestion(Integer id) throws IllegalArgumentException {
		Question question = null;
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM question WHERE question_ID=" + id);
			rs.next();
			question = ObjectsUtil.createQuestion(rs);
			question.setAnswers(getAnswers(id));
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return question;
	}

	@Override
	public Question addQuestion(Question q) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		String sql = "INSERT INTO question(text,hint) VALUES ('" + q.getText() + "','" + q.getHint() + "')";
		int val = statement.executeUpdate(sql);
		ResultSet rs = statement.executeQuery("SELECT * FROM question WHERE text='" + q.getText() + "'");
		rs.next();
		q.setId(rs.getInt(1));
		List<Answer> lsAns = new ArrayList();
		for (int i = 0; i < q.getAnswers().size(); i++) {
			lsAns.add(addAnswer(q.getAnswers().get(i), q.getId()));
		}
		q.setAnswers(lsAns);
		return q;
	}

	@Override
	public void deleteQuestion(Integer questionIdInt) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		int delete = statement.executeUpdate("DELETE FROM question where question_ID=" + questionIdInt);
		deleteAnswers(questionIdInt);
		if (delete == 1)
			System.out.println("Question was deleted");
		else
			System.out.println("Question was not deleted");
	}

	@Override
	public Question editQuestion(Question question, Integer qIdInt) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		int update = statement.executeUpdate("UPDATE question SET " + "text='" + question.getText() + "',hint='" + question.getHint() + "' WHERE question_ID=" + qIdInt);
		for (int i = 0; i < question.getAnswers().size(); i++) {
			editAnswer(question.getAnswers().get(i));
		}
		return question;
	}

	@Override
	public List<Test> getAllTests() {
		List<Test> listTests = new ArrayList();
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM tests");
			Test test;
			while (rs.next()) {
				test = ObjectsUtil.createTest(rs);
				test.setQuestions(getQuestionsFromTest(test.getId()));
				listTests.add(test);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listTests;
	}

	@Override
	public List<Test> getFinalTests() {
		List<Test> listTests = new ArrayList();
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM tests WHERE final_test=" + 1);
			Test test;
			while (rs.next()) {
				test = ObjectsUtil.createTest(rs);
				test.setQuestions(getQuestionsFromTest(test.getId()));
				listTests.add(test);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listTests;
	}

	@Override
	public Test getTest(Integer testIdInt) throws IllegalArgumentException {
		Test test = null;
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM tests WHERE test_ID=" + testIdInt);
			rs.next();
			test = ObjectsUtil.createTest(rs);
			test.setQuestions(getQuestionsFromTest(test.getId()));
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return test;
	}

	@Override
	public Test addTest(Test t) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		String sql = "INSERT INTO tests(test_title,final_test,allowHints) VALUES ('" + t.getTitle() + "'," + t.isFinalTest() + "," + t.isAllowHints() + ")";
		int val = statement.executeUpdate(sql);
		ResultSet rs = statement.executeQuery("SELECT * FROM tests WHERE test_title='" + t.getTitle() + "'");
		rs.next();
		t.setId(rs.getInt(1));
		return t;
	}

	@Override
	public void deleteTest(Integer testIdInt) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		int delete = statement.executeUpdate("DELETE FROM tests where test_ID=" + testIdInt);
		statement = con.createStatement();
		delete = statement.executeUpdate("DELETE FROM test_questions where test_ID=" + testIdInt);
		if (delete == 1)
			System.out.println("Test was deleted");
		else
			System.out.println("Test was not deleted");
	}

	@Override
	public Test editTest(Test test, Integer testIdInt) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		int update = statement.executeUpdate("UPDATE tests SET " + "test_title='" + test.getTitle() + "',final_test=" + test.isFinalTest() + ", allowHints=" + test.isAllowHints() + " WHERE test_ID=" + testIdInt);
		return test;
	}

	@Override
	public void addQuestionToTest(Integer testId, Integer questionId) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		int insert = statement.executeUpdate("INSERT INTO test_questions(test_ID,question_ID) " + "VALUES (" + testId + "," + questionId + ")");
	}

	@Override
	public void deleteQuestionFromTest(Integer testId, Integer questionId) throws SQLException, IllegalArgumentException {
		statement = con.createStatement();
		int delete = statement.executeUpdate("DELETE FROM test_questions" + " WHERE test_ID=" + testId + " AND question_ID=" + questionId);
	}

	private List<Answer> getAnswers(Integer questionId) throws SQLException {
		List<Answer> listAnswers = new ArrayList();
		statement = con.createStatement();
		ResultSet rs = statement.executeQuery("SELECT * FROM answer WHERE question_ID=" + questionId);
		Answer answer;
		while (rs.next()) {
			answer = ObjectsUtil.createAnswer(rs);
			listAnswers.add(answer);
		}

		return listAnswers;
	}

	private void deleteAnswers(Integer questionId) throws SQLException {
		statement = con.createStatement();
		int delete = statement.executeUpdate("DELETE FROM answer where question_ID=" + questionId);
		if (delete == 1)
			System.out.println("Test was deleted");
		else
			System.out.println("Test was not deleted");
	}

	private Answer addAnswer(Answer answer, Integer questionId) throws SQLException {
		statement = con.createStatement();
		String sql = "INSERT INTO answer(answer_text,correct,question_ID) VALUES ('" + answer.getText() + "'," + answer.isCorect() + "," + questionId + ")";
		int val = statement.executeUpdate(sql);
		ResultSet rs = statement.executeQuery("SELECT * FROM answer WHERE answer_text='" + answer.getText() + "' and " + "question_ID=" + questionId);
		rs.next();
		answer.setId(rs.getInt(1));
		return answer;
	}

	private Answer editAnswer(Answer answer) throws SQLException {
		statement = con.createStatement();
		int update = statement.executeUpdate("UPDATE answer SET " + "answer_text='" + answer.getText() + "',correct=" + answer.isCorect() + " WHERE answer_id=" + answer.getId());
		return answer;
	}

	private List<Question> getQuestionsFromTest(Integer testId) throws SQLException {
		List<Question> listQuestion = new ArrayList();
		statement = con.createStatement();
		ResultSet rs = statement.executeQuery("SELECT question.question_ID, question.text, question.hint " + "FROM question " + "INNER JOIN test_questions ON question.question_ID = test_questions.question_ID " + "WHERE test_questions.test_ID =" + testId);
		Question question;
		while (rs.next()) {
			question = ObjectsUtil.createQuestion(rs);
			question.setAnswers(getAnswers(question.getId()));
			listQuestion.add(question);
		}

		return listQuestion;
	}

/*	private List<CompletedTest> getUserTests(Integer userId) throws SQLException {
		List<CompletedTest> listTests = new ArrayList();
		statement = con.createStatement();
		ResultSet rs = statement.executeQuery("SELECT * FROM usertakentests WHERE user_id=" + userId);
		CompletedTest test = new CompletedTest();
		while (rs.next()) {
			test.setId(rs.getInt(3));
			test.setResult(rs.getFloat(4));
			test.setDate(rs.getDate(5));
			test.setTitle(getTest(test.getId()).getTitle());
			listTests.add(test);
		}

		return listTests;
	}

	private CompletedTest addUserCompletedTest(Integer userId, CompletedTest test) throws SQLException {
		statement = con.createStatement();
		String sql = "INSERT INTO usertakentests(user_id, test_id, result, date) VALUES (" + userId + "," + test.getId() + "," + test.getResult() + ",'" + test.getDate() + "')";
		int val = statement.executeUpdate(sql);
		return test;
	}*/

	@Override
	public void addCompletedTest(CompletedTest ct) throws SQLException {
		java.sql.Date sqlDate = new java.sql.Date(ct.getDate().getTime());
		statement = con.createStatement();
		String sql = "INSERT INTO usertakentests(username,test_title,result,date) VALUES ('" + ct.getUserName() + "','" + ct.getTitle() + "'," + ct.getResult() + ",'" + sqlDate + "')";
		int val = statement.executeUpdate(sql);
		ResultSet rs = statement.executeQuery("SELECT * FROM usertakentests WHERE username='" + ct.getUserName() + "' and " + "date='" + ct.getDate() + "'");
		rs.next();
		ct.setId(rs.getInt(1));

	}

	@Override
	public List<CompletedTest> getAllCompletedTests() {
		List<CompletedTest> listCompletedTests = new ArrayList();
		try {
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM usertakentests");
			CompletedTest ctest;
			while (rs.next()) {
				ctest = ObjectsUtil.createCompletedTest(rs);
				listCompletedTests.add(ctest);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listCompletedTests;
	}

}
